﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Configuration;
using System.Data.SqlClient;

namespace Du_Lich
{
    public partial class XepLichNhanVienForm : Form
    {
        public XepLichNhanVienForm()
        {
            InitializeComponent();
        }

        string StrConn = ConfigurationManager.ConnectionStrings["DuLich"].ConnectionString;
        SqlConnection conn = null;
        SqlCommand cmd = null;
        SqlDataAdapter da = null;

        public void MoKetNoi()
        {
            conn = new SqlConnection(StrConn);
            if (conn.State == ConnectionState.Closed)
                conn.Open();
        }

        public void DongKetNoi()
        {
            if (conn.State == ConnectionState.Open)
                conn.Close();
        }

        public DataTable LoadTenTour(string matour)
        {
            string strLoad = "select * from TOUR where matour='"+matour+"'";
            conn = new SqlConnection(StrConn);
            da = new SqlDataAdapter(strLoad, conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
        public DataTable LoadMaTour()
        {
            string strLoad = "select * from TOUR";
            conn = new SqlConnection(StrConn);
            da = new SqlDataAdapter(strLoad, conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

        public DataTable LoadTenNV()
        {
            string strLoad = "select * from NHANVIEN";
            conn = new SqlConnection(StrConn);
            da = new SqlDataAdapter(strLoad, conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

        public DataTable LoadDS()
        {
            string strLoad = "select * from XEPLICHNV";
            conn = new SqlConnection(StrConn);
            da = new SqlDataAdapter(strLoad, conn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }

        public int TimKiem()
        {
            string chuoitim = cbMaTour.Text;
            string tennv = cbTenNhanVien.Text;
            int i = -1;
            foreach (DataGridViewRow dgv in gvXepLichNhanVien.Rows)
            {
                try
                {
                    i++;
                    if (string.Compare(chuoitim, dgv.Cells[0].Value.ToString()) == 0)
                        return i;
                    if (string.Compare(tennv, dgv.Cells[1].Value.ToString()) == 0)
                        return i;
                }
                catch(Exception)
                {
                    MessageBox.Show("Tour này chưa xếp lịch Nhan Viên vs Không tìm thấy được NV !");
                }
            }
            return -1;
        }
        

        public void Them(string matour,string hotennv, string tentour,string tungay, string denngay)
        {
            string strInsert = "Insert XEPLICHNV values(@matour,@hotennv,@tentour,@tungay,@denngay)";
            cmd = new SqlCommand(strInsert, conn);
            cmd.Parameters.AddWithValue("@matour", matour);
            cmd.Parameters.AddWithValue("@hotennv", hotennv);
            cmd.Parameters.AddWithValue("@tentour", tentour);
            cmd.Parameters.AddWithValue("@tungay", tungay);
            cmd.Parameters.AddWithValue("@denngay", denngay);
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }

        public void Xoa(string matour)
        {
            string strDelete = "Delete XEPLICHNV where MaTour=@matour";
            cmd = new SqlCommand(strDelete, conn);
            cmd.Parameters.AddWithValue("@matour", matour);
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }

        private void XepLichNhanVienForm_FormClosing(object sender, FormClosingEventArgs e)
        {
            DongKetNoi();
        }

        private void XepLichNhanVienForm_Load(object sender, EventArgs e)
        {
            MoKetNoi();

            gvXepLichNhanVien.DataSource = LoadDS();

            cbMaTour.DataSource = LoadMaTour();
            cbMaTour.DisplayMember = "MaTour";
            cbMaTour.ValueMember = "MaTour";

            cbTenNhanVien.DataSource = LoadTenNV();
            cbTenNhanVien.DisplayMember = "HoTen";
            cbTenNhanVien.ValueMember = "MaNV";
           
        }

        private void btnThem_Click(object sender, EventArgs e)
        {
            Them(cbMaTour.Text, cbTenNhanVien.Text, cbTenTour.Text, datetungay.Value.ToString(), datedenngay.Value.ToString());
            gvXepLichNhanVien.DataSource = LoadDS();
        }

        private void gvXepLichNhanVien_SelectionChanged(object sender, EventArgs e)
        {
            if (gvXepLichNhanVien.SelectedRows.Count == 0)
                return;
            int i = gvXepLichNhanVien.SelectedRows[0].Index;
            cbMaTour.Text = gvXepLichNhanVien.Rows[i].Cells[0].Value.ToString();
            cbTenNhanVien.Text = gvXepLichNhanVien.Rows[i].Cells[1].Value.ToString();
            cbTenTour.Text = gvXepLichNhanVien.Rows[i].Cells[2].Value.ToString();
            datetungay.Value = DateTime.Parse(gvXepLichNhanVien.Rows[i].Cells[3].Value.ToString());
            datedenngay.Value = DateTime.Parse(gvXepLichNhanVien.Rows[i].Cells[4].Value.ToString());
        }

        private void btnXoa_Click(object sender, EventArgs e)
        {
            int i = gvXepLichNhanVien.SelectedRows[0].Index;
            Xoa(gvXepLichNhanVien.Rows[i].Cells[0].Value.ToString());
            gvXepLichNhanVien.DataSource = LoadDS();
        }

        private void btnTim_Click(object sender, EventArgs e)
        {
            int kq = TimKiem();
            if (kq == -1)
                MessageBox.Show("Khong tim thay", "Thong bao tim kiem");
            else
                gvXepLichNhanVien.Rows[kq].Selected = true;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            conn.Close();
            this.Close();
        }

        private void cbMaTour_SelectedIndexChanged(object sender, EventArgs e)
        {
            try{
            cbTenTour.DataSource = LoadTenTour(cbMaTour.SelectedValue.ToString());
            cbTenTour.DisplayMember = "TenTour";
            cbTenTour.ValueMember = "MaTour";
            }catch{}
        }
    }
}
